package cn.waka.sys.admin.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;

import cn.waka.sys.admin.object.Page;
import cn.waka.sys.admin.object.Scene;

@Component
public class BoxDao extends BaseDao {
	
	
	//添加配置
	public int saveScene(final Scene scene) {
		KeyHolder holder = new GeneratedKeyHolder();
		int rs = this.update(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				String sql = "insert into s_scene_config (scene_number,role_status,scene_order,resource_id,audio_url,relation_number,play_time,audio_time,`describe`)values(?,?,?,?,?,?,?,?,?)";
				PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				
				ps.setString(1, scene.getSceneNumber());
				ps.setInt(2, scene.getRoleStatus());
				ps.setString(3, scene.getSceneOrder());
				ps.setInt(4, scene.getResource_id());
				ps.setString(5, scene.getAudioUrl());
				ps.setString(6, scene.getRelation_number());
				ps.setInt(7, scene.getPlayTime());
				ps.setFloat(8, scene.getAudioTime());
				ps.setString(9, scene.getDescribe());
				
				return ps;
			}
		}, holder);
		//获取新数据的ID
		scene.setId(holder.getKey().intValue());
		return rs;
	}
	
	//动画配置列表
	public List<Object> getSceneList(Page p){
		int page=p.getPage();
		int start=(page-1)*p.getSize();
		String sql="SELECT id,scene_number,role_status,scene_order,resource_id,audio_url,relation_number,play_time,audio_time,scene.describe AS 'describe' FROM s_scene_config scene LIMIT ?,?";
		List<Object> list=this.query(sql, new RowMapper<Object>(){
			@Override
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String,Object> row=new HashMap<String,Object>();
				row.put("id", rs.getInt(1));
				row.put("scene_number", rs.getString(2));
				int status = rs.getInt(3);
				if (status==0) {
					row.put("role_status", "待机");
				}else if (status==1) {
					row.put("role_status", "脚本");
				}else if (status==2) {
					row.put("role_status", "引导");
				}else if (status==3) {
					row.put("role_status", "互动");
				}else if (status==4) {
					row.put("role_status", "提示");
				}
				row.put("scene_order", rs.getString(4));
				row.put("resource_id", rs.getInt(5));
				row.put("audio_url", rs.getString(6));
				row.put("relation_number", rs.getString(7));
				int playTime = rs.getInt(8);
				if (playTime==0) {
					row.put("play_time", "不限 ");
				}else if (playTime==1) {
					row.put("play_time", "早上");
				}else if (playTime==2) {
					row.put("play_time", "中午");
				}else if (playTime==3) {
					row.put("play_time", "晚上");
				}
				row.put("audio_time", rs.getFloat(9));
				row.put("describe", rs.getString(10));
				return row;
			}
			
		},start,p.getSize());
		return list;
	}
	
	//查询总数
	public int findCount(Page p){
		String sql="select count(1) from s_scene_config";
		return this.queryForObject(sql,Integer.class);
	}
	
	//查询一个
	public Scene getById(int id){
		String sql="SELECT sc.`id`,scene_number,role_status,scene_order,resource_id,audio_url,relation_number,play_time,audio_time, sc.`describe`,sr.`title` FROM s_scene_config sc LEFT JOIN s_resource sr ON sc.`resource_id`=sr.`id`  WHERE sc.`id`=?";
		List<Scene> list=this.query(sql, new RowMapper<Scene>(){
			@Override
			public Scene mapRow(ResultSet rs, int rowNum) throws SQLException {
				Scene scene=new Scene();
				scene.setId(rs.getInt(1));
				scene.setSceneNumber(rs.getString(2));
				scene.setRoleStatus(rs.getInt(3));
				scene.setSceneOrder(rs.getString(4));
				scene.setResource_id(rs.getInt(5));
				scene.setAudioUrl(rs.getString(6));
				scene.setRelation_number(rs.getString(7));
				scene.setPlayTime(rs.getInt(8));
				scene.setAudioTime(rs.getFloat(9));
				scene.setDescribe(rs.getString(10));
				scene.setResource_name(rs.getString(11));
				return scene;
			}
		},id);
		return list.size()>0?list.get(0):null;
	}
	
	//修改
	public int update(final Scene s) {
		String sql = "update s_scene_config set scene_number=?,role_status=?,scene_order=?,resource_id=?,audio_url=?,relation_number=?,play_time=?,audio_time=?,`describe`=? where id=?";
		this.update(sql, 
				s.getSceneNumber(), 
				s.getRoleStatus(), 
				s.getSceneOrder(), 
				s.getResource_id(), 
				s.getAudioUrl(),
				s.getRelation_number(), 
				s.getPlayTime(), 
				s.getAudioTime(), 
				s.getDescribe(), 
				s.getId());

		return s.getId();
	}
	
	
	//删除
	public int deleteScene(int id){
		String sql="delete from s_scene_config where id = ?";
		return this.update(sql,id);
	}
	
	public List<Object> getBoxLogList(int box_id,int start,int size){	
		String sql = "SELECT id,box_id,`action`,create_time FROM b_box_log WHERE box_id=? ORDER BY create_time desc LIMIT ?,?";

		List<Object> list = this.query(sql, new RowMapper<Object>() {
			@Override
			public Map<String,Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String,Object> row=new HashMap<String,Object>();
				row.put("id", rs.getInt(1));
				row.put("box_id", rs.getInt(2));
				row.put("action", rs.getInt(3));
				row.put("create_time", rs.getTimestamp(4));
				return row;
			}
		},box_id,start,size);

		return list;
	}
	
	//查询总数
	public int findCount(int id){
		String sql="SELECT COUNT(1) FROM b_box_log WHERE box_id =?";
		return this.queryForObject(sql,Integer.class,id);
	}
	
}
